/**
 * Copyright (c) 2016-2019 人人开源 All rights reserved.
 *
 * https://www.renren.io
 *
 * 版权所有，侵权必究！
 */

package com.yonyou.portcity.repository;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.yonyou.portcity.entity.RegisterUser;
import com.yonyou.portcity.entity.UserEntity;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * 用户
 *
 * @author Mark sunlightcs@gmail.com
 */
public interface UserRepository extends BaseMapper<UserEntity> {
        @Select("select a.clickdate,ifnull(b.count,0) as registercount\n" +
                "from (\n" +
                "\t\t\t\t\tSELECT  DATE(subdate(curdate(),date_format(curdate(),'%w')-1)) as clickdate  \n" +
                "            union all  \n" +
                "            SELECT  DATE(DATE_ADD(subdate(curdate(),date_format(curdate(),'%w')-1), interval 1 day)) as clickdate  \n" +
                "            union all  \n" +
                "            SELECT  DATE(DATE_ADD(subdate(curdate(),date_format(curdate(),'%w')-1), interval 2 day)) as clickdate  \n" +
                "            union all  \n" +
                "            SELECT  DATE(DATE_ADD(subdate(curdate(),date_format(curdate(),'%w')-1), interval 3 day)) as clickdate  \n" +
                "            union all  \n" +
                "            SELECT  DATE(DATE_ADD(subdate(curdate(),date_format(curdate(),'%w')-1), interval 4 day)) as clickdate  \n" +
                "            union all  \n" +
                "            SELECT DATE(DATE_ADD(subdate(curdate(),date_format(curdate(),'%w')-1), interval 5 day)) as clickdate  \n" +
                "            union all  \n" +
                "            SELECT DATE(DATE_ADD(subdate(curdate(),date_format(curdate(),'%w')-1), interval 6 day)) as clickdate ) a \n" +
                "left join (\n" +
                "  select date(create_time) as datetime, count(*) as count\n" +
                "  from tb_user\n" +
                "  group by date(create_time)\n" +
                ") b on a.clickdate = b.datetime  order by a.clickdate")
        List<RegisterUser> selectCountByCreateTime();
}
